﻿<?php
    include_once '../../../../controlador/php/conexion/Conexion10.php';
    $con = new Conexion10();
    
    $prio = $_REQUEST['prio'];
    $cam = $_REQUEST['cam'];
    $areas = $_REQUEST['areas'];
    $ingre = $_REQUEST['ingre'];
    $f1 =$_REQUEST['f1'];
    $f2 = $_REQUEST['f2'];
    $doc = $_REQUEST['doc'];
    $nom1 = $_REQUEST['nom1'];
    $nom2 = $_REQUEST['nom2'];
    $ape1 = $_REQUEST['ape1'];
    $ape2 = $_REQUEST['ape2'];
    $esta = $_REQUEST['esta'];
    $exam = $_REQUEST['exam'];
    $solA = $_REQUEST['solA'];
    $fsa1 = $_REQUEST ['fsa1'];
    $fsa2 = $_REQUEST['fsa2'];
    $auto = $_REQUEST['auto'];
    $fu1 = $_REQUEST['fu1'];
    $fu2 = $_REQUEST['fu2'];
    $solC = $_REQUEST['solC'];
    $fc1 = $_REQUEST['fc1'];
    $fc2 = $_REQUEST['fc2'];
    $asigc = $_REQUEST['asigc'];
    $fac1 = $_REQUEST['fac1'];
    $fac2 = $_REQUEST['fac2'];
    $tra = $_REQUEST['tra'];
    $ft1 = $_REQUEST['ft1'];
    $ft2 = $_REQUEST['ft2'];
    $indi = $_REQUEST['indi'];
    $fi1 = $_REQUEST['fi1'];
    $fi2 = $_REQUEST['fi2'];
    $fac = $_REQUEST['fac'];
    $fef1 = $_REQUEST['fef1'];
    $fef2 = $_REQUEST['fef2'];
    $tom = $_REQUEST['tom'];
    $ftm1 = $_REQUEST['ftm1'];
    $ftm2 = $_REQUEST['ftm2'];
    $int = $_REQUEST['int'];
    $fit1 = $_REQUEST['fit1'];
    $fit2 = $_REQUEST['fit2'];
    $med = $_REQUEST['med'];
    $medA = $_REQUEST['medA'];
    $fMa1 = $_REQUEST['fMa1'];
    $fMa2 = $_REQUEST['fMa2'];
    $area= $_REQUEST['area'];
    $n = $_REQUEST['n'];
    
    $fecha1 = date('Y-m-d 00:00:00.000',strtotime($f1));
    $fecha2 = date('Y-m-d 23:59:00.000',strtotime($f2));
    $fechasa1 = date('Y-m-d 00:00:00.000',strtotime($fsa1));
    $fechasa2 = date('Y-m-d 23:59:00.000',strtotime($fsa2));
    $fechau1 = date('Y-m-d 00:00:00.000',strtotime($fu1));
    $fechau2 = date('Y-m-d 23:59:00.000',strtotime($fu2));
    $fechac1 = date('Y-m-d 00:00:00.000',strtotime($fc1));
    $fechac2 = date('Y-m-d 23:59:00.000',strtotime($fc2));
    $fechaAc1 = date('Y-m-d 00:00:00.000',strtotime($fac1));
    $fechaAc2 = date('Y-m-d 23:59:00.000',strtotime($fac2));
    $fechat1 = date('Y-m-d 00:00:00.000',strtotime($ft1));
    $fechat2 = date('Y-m-d 23:59:00.000',strtotime($ft2));
    $fechai1 = date('Y-m-d 00:00:00.000',strtotime($fi1));
    $fechai2 = date('Y-m-d 23:59:00.000',strtotime($fi2));
    $fechaf1 = date('Y-m-d 00:00:00.000',strtotime($fef1));
    $fechaf2 = date('Y-m-d 23:59:00.000',strtotime($fef2));
    $fechatm1 = date('Y-m-d 00:00:00.000',strtotime($ftm1));
    $fechatm2 = date('Y-m-d 23:59:00.000',strtotime($ftm2));
    $fechait1 = date('Y-m-d 00:00:00.000',strtotime($fit1));
    $fechait2 = date('Y-m-d 23:59:00.000',strtotime($fit2));
    $fechaMa1 = date('Y-m-d 00:00:00.000',strtotime($fMa1));
    $fechaMa2 = date('Y-m-d 23:59:00.000',strtotime($fMa2));
    
    if($n == 1){
        $Tabla = "HCNSOLEXA";        
    }
    else if($n == 2){
        $Tabla = "HCNSOLPNQX";
    }
   if($prio != ""){
        $consulta = "AND HCSESTADO = '".$prio."'";
    }if($f1 != "" & $f2 != ""){
         $consulta2 ="AND $Tabla.HCSFECSOL BETWEEN  '".$fecha1."' AND '".$fecha2."'";
    }if($cam != ""){
         $consulta3 ="AND HCACODIGO LIKE '%".$cam."%'";
    }if ($areas != ""){
        $consulta4 = "AND HPNSUBGRU.OID IN (".$areas.")";
    }if ($ingre != ""){
        $consulta5 = "AND ADNINGRESO.AINCONSEC = '".$ingre."'";
    }if ($doc != ""){
        $consulta6 = "AND GENPACIEN.PACNUMDOC = '".$doc."'";
    }if ($nom1 != ""){
        $consulta7 = "AND PACPRINOM LIKE '%".$nom1."%'";
    }if ($nom2 != ""){
        $consulta8 = "AND PACSEGNOM LIKE '%".$nom2."%'";
    }if ($ape1 != ""){
        $consulta9 = "AND PACPRIAPE LIKE '%".$ape1."%'";
    }if ($ape2 != ""){
        $consulta10 = "AND PACSEGAPE LIKE '%".$ape2."%'";
    }if ($esta != ""){
        $consulta11 = "AND SLNORDSER.SOSESTADO = '".$esta."'";
    }if ($exam != ""){
        $consulta12 = "AND GENSERIPS.SIPNOMBRE LIKE '%".$exam."%'";
    }if ($solA != ""){
        if($solA == "1" & $fsa1 == "" & $fsa2 == "" ){
            $consulta13 = "AND solautori = '".$solA."'";
        }if($solA == "1" & $fsa1 != "" & $fsa2 != "" ){
            $consulta13 = "AND solautori = '".$solA."'   and fecsolau between '".$fechasa1."' and '".$fechasa2."'";
        }if($solA == "0" & $fsa1 == "" & $fsa2 == "" ){
            $consulta13 = "AND solautori IS NULL";
        }if($solA == "0" & $fsa1 != "" & $fsa2 != "" ){
             $consulta13 = "AND solautori IS NULL AND $Tabla.HCSFECSOL BETWEEN  '".$fechasa1."' AND '".$fechasa2."'";
        }
    }if ($auto != ""){
        if($auto == "1" & $fu1 == "" & $fu2 == "" ){
            $consulta14 = "AND autorizar = '1'";
        }if($auto == "1" & $fu1 != "" & $fu2 != "" ){
            $consulta14 = "AND autorizar = '1' AND fecauto BETWEEN '".$fechau1."' AND '".$fechau2."'";
        }if($auto == "2" & $fu1 == "" & $fu2 == "" ){
            $consulta14 = "AND autorizarno = '1' AND autorizar is null";
        }if($auto == "2" & $fu1 != "" & $fu2 != "" ){
             $consulta14 = "AND autorizarno = '1' AND autorizar is null fecautono BETWEEN  '".$fechau1."' AND '".$fechau2."'";
        }if($auto == "3" & $fu1 == "" & $fu2 == "" ){
            $consulta14 = "AND autorizarn = 1 AND autorizarno is null AND autorizar is null";
        }if($auto == "3" & $fu1 != "" & $fu2 != "" ){
             $consulta14 = "AND autorizarn = '1' AND autorizarno = '1' AND autorizar is null AND fecauton BETWEEN  '".$fechau1."' AND '".$fechau2."'";
        }
    }if ($solC != ""){
        if($solC == "1" & $fc1 == "" & $fc2 == "" ){
            $consulta15 = "AND solcita = '".$solC."'";
        }if($solC == "1" & $fc1 != "" & $fc2 != "" ){
            $consulta15 = "AND solcita = '".$solC."' AND fecsolcita BETWEEN '".$fechac1."' and '".$fechac2."'";
        }if($solC == "0" & $fc1 == "" & $fc2 == "" ){
            $consulta15 = "AND solcita IS NULL";
        }if($solC == "0" & $fc1 != "" & $fc2 != "" ){
             $consulta15 = "AND solcita IS NULL AND $Tabla.HCSFECSOL BETWEEN  '".$fechac1."' AND '".$fechac2."'";
        }
    }if ($asigc != ""){
        if($asigc == "1" & $fac1 == "" & $fac2 == "" ){
            $consulta16 = "AND asigfecha2  is not null";
        }if($asigc == "1" & $fac1 != "" & $fac2 != "" ){
            $consulta16 = "AND asigfecha2  is not null AND asigfecha2 between  '".$fechaAc1."' and '".$fechaAc2."'";
        }if($asigc == "0" & $fac1 == "" & $fac2 == "" ){
            $consulta16 = "AND asigfecha2  is  null";
        }if($asigc == "0" & $fac1 != "" & $fac2 != "" ){
             $consulta16 = "AND asigfecha2  is  null AND  $Tabla.HCSFECSOL BETWEEN  '".$fechaAc1."' AND '".$fechaAc2."'";
        }
    }if ($tra != ""){
        if($tra == "1" & $ft1 == "" & $ft2 == "" ){
            $consulta17 = "AND trestado  ='".$tra."'";
        }if($tra == "1" & $ft1 != "" & $ft2 != "" ){
            $consulta17 = "AND trestado  ='".$tra."' AND trfecha between  '".$fechat1."' and '".$fechat2."'";
        }if($tra == "0" & $ft1 == "" & $ft2 == "" ){
            $consulta17 = "AND trestado is null";
        }if($tra == "0" & $ft1 != "" & $ft2 != "" ){
             $consulta17 = "AND trestado is null AND  $Tabla.HCSFECSOL BETWEEN  '".$fechat1."' AND '".$fechat2."'";
        }
    }if ($indi != ""){
        if($indi == "1" & $fi1 == "" & $fi2 == "" ){
            $consulta18 = "AND segestado  = '".$indi."'";
        }if($indi == "1" & $fi1 != "" & $fi2 != "" ){
            $consulta18 = " AND segestado  = '".$indi."' AND segfecindi between  '".$fechai1."' and '".$fechai2."'";
        }if($indi == "0" & $fi1 == "" & $fi2 == "" ){
            $consulta18 = "AND segestado  is null";
        }if($indi == "0" & $fi1 != "" & $fi2 != "" ){
             $consulta18 = "AND segestado  is null AND $Tabla.HCSFECSOL BETWEEN  '".$fechai1."' AND '".$fechai2."'";
        }
    }if ($fac != ""){
        if($fac == "1" & $fef1 == "" & $fef2 == "" ){
            $consulta19 = "AND facturado = '".$fac."'";
        }if($fac == "1" & $fef1 != "" & $fef2 != "" ){
            $consulta19 = "AND facturado = '".$fac."' AND fecfactu between  '".$fechaf1."' and '".$fechaf2."'";
        }if($fac == "0" & $fef1 == "" & $fef2 == "" ){
            $consulta19 = "AND facturado is null";
        }if($fac == "0" & $fef1 != "" & $fef2 != "" ){
             $consulta19 = "AND facturado is null AND $Tabla.HCSFECSOL between  '".$fechaf1."' and '".$fechaf2."'";
        }
    }if ($tom != ""){
        if($tom == "1" & $ftm1 == "" & $ftm2 == "" ){
            $consulta20 = "AND tmestado  ='1'";
        }if($tom == "1" & $ftm1 != "" & $ftm2 != "" ){
            $consulta20 = "AND tmestado  ='1' AND tmfecha between  '".$fechatm1."' and '".$fechatm2."'";
        }if($tom == "2" & $ftm1 == "" & $ftm2 == "" ){
            $consulta20 = "AND tmestadon = '1' AND tmestado = '0'";
        }if($tom == "2" & $ftm1 != "" & $ftm2 != "" ){
             $consulta20 = "AND tmestadon = '1' AND tmestado = '0' AND tmfechan between  '".$fechatm1."' and '".$fechatm2."'";
        }
    }if ($int != ""){
        if($int == "1" & $fit1 == "" & $fit2 == "" ){
            $consulta21 = "AND inestado  ='".$int."'";
        }if($int == "1" & $fit1 != "" & $fit2 != "" ){
            $consulta21 = "AND inestado  ='".$int."' AND infecha between  '".$fechait1."' and '".$fechait2."'";
        }if($int == "0" & $fit1 == "" & $fit2 == "" ){
            $consulta21 = "AND inestado is null";
        }if($int == "0" & $fit1 != "" & $fit2 != "" ){
             $consulta21 = "AND inestado is null AND $Tabla.HCSFECSOL between  '".$fechait1."' and '".$fechait2."'";
        }
   } if($med != ""){
        $consulta22 ="AND segmedico = '".$med."'";
    }
    if ($medA != ""){
        if($medA == "1" & $fMa1 == "" & $fMa2 == "" ){
            $consulta23 = "AND segvermed  ='".$medA."'";
        }if($medA == "1" & $fMa1 != "" & $fMa2 != "" ){
            $consulta23 = "AND segvermed  ='".$medA."' AND segfecvermed between  '".$fechaMa1."' and '".$fechaMa2."'";
        }if($medA == "2" & $fMa1 == "" & $fMa2 == "" ){
            $consulta23 = "AND segvermed = '".$medA."'";
        }if($medA == "2" & $fMa1 != "" & $fMa2 != "" ){
             $consulta23 = "AND segvermed = '".$medA."' AND $Tabla.HCSFECSOL between  '".$fechaMa1."' and '".$fechaMa2."'";
        }
   }
    if($area != ""){
        //$area2 = implode($area);
        $consulta24 = "And b.OID IN (".$area.")";
    }
    
    $listado = "SELECT datediff(MINUTE,GETDATE(),asigfecha2) as h, $Tabla.OID,ADNINGRESO.AINCONSEC AS INGRESO, $Tabla.HCSCANTI AS CANTIDAD,
                CONVERT(NVARCHAR(1000),".$Tabla.".HCSOBSERV) AS OBSERVACIONES,".$Tabla.".HCSFECSOL AS FECHA_SOLICITUD,
                HCNFOLIO.HCNUMFOL AS NUM_FOLIO,GENSERIPS.SIPCODIGO AS CODIGO, ltrim(rtrim(GENSERIPS.SIPNOMBRE)) AS MUESTRA,
                GENPACIEN.PACNUMDOC AS DOCUMENTO,RTrim(GENPACIEN.PACPRINOM +' '+
                GENPACIEN.PACSEGNOM +' '+ GENPACIEN.PACPRIAPE +' '+ GENPACIEN.PACSEGAPE) AS NOM_PACIENTE,
                HPNDEFCAM.HCACODIGO AS CAMA,GENARESER.GASCODIGO, GENARESER.GASNOMBRE,GENMEDICO.GMENOMCOM AS MEDICO,Case
                When $Tabla.HCSESTADO = '0' Then 'URGENTE' Else 'RUTINARIO'
                End ESTADO, GENPACIEN.GPAFECNAC,Case
                When ADNINGRESO.AINTIPING = '1' Then 'AMBULATORIO' Else 'HOSPITALARIO'
                End CLASE_INGRESO,Case When ADNINGRESO.AINURGCON = '0' Then 'URGENCIAS'
                When ADNINGRESO.AINURGCON = '1' Then 'CONSULTA_EXTERNA'
                When ADNINGRESO.AINURGCON = '2' Then 'NACIDO_HOSPITAL'
                When ADNINGRESO.AINURGCON = '3' Then 'REMITIDO'
                When ADNINGRESO.AINURGCON = '4' Then 'HOSPI_URGENCIAS'
                When ADNINGRESO.AINURGCON = '5' Then 'HOSPITALIZACIÓN'
                When ADNINGRESO.AINURGCON = '6' Then 'IMAGENES'
                When ADNINGRESO.AINURGCON = '7' Then 'LABORATORIO'
                End INGRESO_POR,Case SLNORDSER.SOSESTADO When 0 Then 'REGISTRADO'
                When 1 Then 'CONFIRMADO' When 2 Then 'ANULADO'
                End As ESTADOORDEN,CONVERT(NVARCHAR(100),SLNORDSER.SOSDESORD) AS ORDEN_SERVICIO,
                SLNORDSER.SOSORDSER,segfecindi,segestado,trestado,trfecha,tmestado,tmfecha,inestado,infecha,asigfecha,asigfecha2,cast(asigobserv as text)as asigobserv,
                solcita,fecsolcita,usu_oidsc,cast(obsesolcita as text)as obsesolcita,facturado,usu_oidf,fecfactu,solautori,fecsolau,usu_oidsa,
                autorizar,fecauto,usu_oidau,asirea,tmpnt,tmcual,segmedico,segfecmedico,segusumedico,segvermed,segfecvermed,segusumed 
                ,cast(segobservacion as text)as segobservacion, asighora,USUNOMBRE, USUDESCRI,tmestadon,usu_oidn,tmfechan,autorizarn,fecauton,usu_oidaun,autorizarno,fecautono,usu_oidauno,
                cast(segimagenes.segobserva as text)as segobserva,usu_oidobs,segfecobs,
                traisladono,trfechano,usu_oidno,traisladosi,trtipoaislami,trfechapoais,usu_oidpoais,trrazonaisla,obs.segobserva as ob,RepetirExa,FechaRep,usu_oidRep,InfoPaciente,FechaInfo,usu_oidinfo,ExamenC,FechaExamenC
                From GENPACIEN 
                Inner Join ADNINGRESO On GENPACIEN.OID = ADNINGRESO.GENPACIEN 
                Inner Join HCNFOLIO On ADNINGRESO.OID = HCNFOLIO.ADNINGRESO 
                Inner Join GENMEDICO On HCNFOLIO.GENMEDICO = GENMEDICO.OID 
                Inner Join $Tabla On $Tabla.HCNFOLIO = HCNFOLIO.OID
                Inner Join GENSERIPS On $Tabla.GENSERIPS = GENSERIPS.OID 
                Inner Join SLNSERHOJ On $Tabla.SLNSERHOJ = SLNSERHOJ.OID 
                Inner Join SLNSERPRO On SLNSERHOJ.OID = SLNSERPRO.OID 
                Inner Join SLNORDSER On SLNSERPRO.SLNORDSER1 = SLNORDSER.OID And SLNSERPRO.ADNINGRES1 = SLNORDSER.ADNINGRES1 
                Left Outer Join HPNESTANC On ADNINGRESO.OID = HPNESTANC.ADNINGRES 
                Left Outer Join HPNDEFCAM On HPNESTANC.HPNDEFCAM = HPNDEFCAM.OID
                left join HPNGRUPOS on HPNDEFCAM.HPNGRUPOS = HPNGRUPOS.OID
                left join HPNSUBGRU ON HPNSUBGRU.OID = HPNDEFCAM.HPNSUBGRU 
                left join GENARESER ON GENARESER.OID = HPNSUBGRU.GENARESER 
                left join planillaUsuario..segimagenes on $Tabla.OID  = planillaUsuario..segimagenes.hcnsolexa
                left join planillaUsuario..segtranslado on $Tabla.OID = planillaUsuario..segtranslado.hcnsolexa
                left join planillaUsuario..segtomado on $Tabla.OID = planillaUsuario..segtomado.hcnsolexa
                left join planillaUsuario..seginterpre on $Tabla.OID = planillaUsuario..seginterpre.hcnsolexa
                left join planillaUsuario.. segasigcita on $Tabla.OID = planillaUsuario..segasigcita.hcnsolexa
                left outer join  GENARESER as b on GENSERIPS.GENARESER1 = b.OID
                left join GENUSUARIO on segimagenes.segmedico = GENUSUARIO.USUNOMBRE
                outer apply(select top 1 * from planillaUsuario..segobservacion
                            where segobservacion.hcnsolexa = $Tabla.OID)as obs
                Where 
               GENSERIPS.GENARESER1  IN (6,7,8,9,10,11,12,14,51,63,90) And HPNESTANC.HESFECSAL Is Null   
               ".$consulta." ".$consulta2." ".$consulta3." ".$consulta4." ".$consulta5." ".$consulta6." ".$consulta7." ".$consulta8."
               ".$consulta9." ".$consulta10." ".$consulta11." ".$consulta12." ".$consulta13." ".$consulta14." ".$consulta15." ".$consulta16."
               ".$consulta17." ".$consulta18." ".$consulta19." ".$consulta20." ".$consulta21." ".$consulta22." ".$consulta23." ".$consulta24." AND ($Tabla.HCSFECSOL > CONVERT(DATETIME, GETDATE() - 15, 102))
                Order By $Tabla.HCSFECSOL DESC";
   $res = $con->query($listado);
   
    while($re = mssql_fetch_array($res)){
       
        $lista[] = array_map(utf8_encode,$re);
         
    }
    print_r(json_encode($lista));
    $con->cerrarConexion();
    
?>
